package com.demoweb.model.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.demoweb.model.dao.ConnectionHelper;
import com.demoweb.model.dto.Score;

public class ScoreDao {

	public List<Score> getMembersScore(int gameno) {// 게시판 랭킹 
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<Score> scores = new ArrayList<Score>();
		
		try {
			//1. 드라이버 로딩
					//account
			conn = ConnectionHelper.getConnection();

			//3. 명령 생성
			String sql = 
			"SELECT memberid, score "+
			"FROM score "+
			"where gameno = ? "+
			"ORDER BY score DESC "; 
			pstmt = conn.prepareStatement(sql);		
			pstmt.setInt(1, gameno);
			//4. 명령 실행
			rs = pstmt.executeQuery();
			//5. 조회 결과를 처리 (SELECT QUERY인 경우)
			while (rs.next()) {
				Score score= new Score();
				score.setMemberId(rs.getString(1));
				score.setScore(rs.getInt(2));
				
				scores.add(score);
			}
		} catch (Exception ex) {
			
		} finally {
			try { rs.close(); } catch (Exception ex) {}
			try { pstmt.close(); } catch (Exception ex) {}
			try { conn.close(); } catch (Exception ex) {}
		}
		
		return scores;
	}
	
public List<Score> getMyScore(int gameno, String id) {// 게시판 랭킹 
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<Score> scores = new ArrayList<Score>();
		
		try {
			//1. 드라이버 로딩
//			Class.forName("com.mysql.jdbc.Driver");
//			//2. 연결 생성
//			conn = DriverManager.getConnection(
//				"jdbc:mysql://172.16.3.16:3306/netmarble",	//url
//				"Lee", "8903");						//account
			conn = ConnectionHelper.getConnection();
			//3. 명령 생성
			String sql = 
//				"SELECT memberid, score " + 
//				"FROM score";
			"SELECT memberid, score "+
			"FROM score "+
			"where gameno = ? and memberid = ?"+
			"ORDER BY score DESC "; 
			pstmt = conn.prepareStatement(sql);		
			pstmt.setInt(1, gameno);
			pstmt.setString(2, id);
			//4. 명령 실행
			rs = pstmt.executeQuery();
			//5. 조회 결과를 처리 (SELECT QUERY인 경우)
			while (rs.next()) {
				Score score= new Score();
				score.setMemberId(rs.getString(1));
				score.setScore(rs.getInt(2));
				
				scores.add(score);
			}
		} catch (Exception ex) {
			
		} finally {
			try { rs.close(); } catch (Exception ex) {}
			try { pstmt.close(); } catch (Exception ex) {}
			try { conn.close(); } catch (Exception ex) {}
		}
		
		return scores;
	}
	
	public Score getMemberById(String id) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Score score = null;
		
		try {
//			//1. 드라이버 로딩
//			Class.forName("com.mysql.jdbc.Driver");
//			//2. 연결 생성
//			conn = DriverManager.getConnection(
//				"jdbc:mysql://172.16.3.16:3306/netmable",	//url
//				"Lee", "8903");						//account
			conn = ConnectionHelper.getConnection();
			//3. 명령 생성
			String sql = 
					"SELECT memberid, gameno, score, scoredate, scorenumber"
							+ "FROM score"
							+ "WHERE memberid = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, id);
			//4. 명령 실행
			rs = pstmt.executeQuery();
			//5. 조회 결과를 처리 (SELECT QUERY인 경우)
			if (rs.next()) {
				score = new Score();
				score.setMemberId(rs.getString(1));
				score.setGameNo(rs.getInt(2));
				score.setScore(rs.getInt(3));
				score.setScoreDate(rs.getTimestamp(4));
				score.setScoreNumber(rs.getInt(5));
			}
			
		} catch (Exception ex) {
			
		} finally {
			try { rs.close(); } catch (Exception ex) {}
			try { pstmt.close(); } catch (Exception ex) {}
			try { conn.close(); } catch (Exception ex) {}
		}
		
		return score;
	}
	
	public void insertScore(Score dto, int gameno) {

		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			// 1. 드라이버 로딩
			Class.forName("com.mysql.jdbc.Driver");
			// 2. 연결 생성
			conn = DriverManager.getConnection(
					"jdbc:mysql://paare.iptime.org/netmarble", // url
					"bumsu", "bumsu");
			
			// 3. 명령 생성
			String sql = "INSERT INTO score " 
					+ "(memberid, score, gameno) "
					+ "VALUES (?, ?, ?);";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, dto.getMemberId());
			pstmt.setInt(2, dto.getScore());
			pstmt.setInt(3, gameno);
			
			// 4. 명령 실행
			pstmt.executeUpdate();
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try {
				pstmt.close();
			} catch (Exception ex) {
			}
			try {
				conn.close();
			} catch (Exception ex) {
			}
		}
	}
	
}
